Importing Text Files and Data from Folders in Power BI

Course: Microsoft Power BI Training

Topic: Importing Text Files and Data from Folders

Course Link: Microsoft Power BI Training at JustAcademy

Demo Link: Register for a Course Demo

JustAcademy Website: Visit JustAcademy

1. Introduction to Importing Text Files and Folder Data

Power BI allows users to import data from multiple file formats, including CSV, TXT, and other structured text files. It can also combine data from multiple files stored in a folder. These capabilities are useful when business information is received as regular exports from accounting systems, CRM platforms, websites, inventory applications, or other business tools.

Instead of manually copying information into a spreadsheet, users can connect Power BI to a file or folder, clean the data in Power Query Editor, and build reports using the resulting tables. When the source files are updated, the data can be refreshed according to the connection and refresh setup.

Learning Objectives

• Understand text files and common file formats used in Power BI.
• Import CSV and TXT files into Power BI Desktop.
• Configure delimiters, encoding, headers, and data types.
• Connect Power BI to a folder containing multiple files.
• Combine files that share a consistent structure.
• Clean and transform imported data using Power Query Editor.
• Handle missing files, inconsistent columns, duplicate records, and import errors.
• Prepare a refresh-friendly data import process.

2. Understanding Text Files

A text file stores information as readable characters. Depending on its structure, it may contain simple lines of text or rows and columns separated by special characters.

2.1 Common Text File Formats

2.2 Example of a CSV File

Suppose a company exports its monthly sales transactions into a file named Sales_January.csv. The file might contain the following records:

OrderID,OrderDate,Region,Product,Quantity,Sales
1001,2026-01-05,West,Laptop,2,120000
1002,2026-01-07,North,Monitor,3,45000
1003,2026-01-09,South,Keyboard,5,7500

Each line represents a record, while each comma separates one field from another. Power BI can recognize these fields as columns when the delimiter is configured correctly.

2.3 What Is a Delimiter?

A delimiter is a character that separates values in a text file. Power BI uses the delimiter setting to determine where one column ends and another begins.

3. Importing a Text File into Power BI Desktop

Power BI Desktop provides connectors for text and CSV files. The exact preview options may vary depending on the file format and Power BI version.

Step 1: Open Power BI Desktop

1. Launch Power BI Desktop.
2. Open an existing report or create a new report.
3. Save the report file using a meaningful name, such as MonthlySalesAnalysis.pbix.

Step 2: Select the Text/CSV Connector

1. Go to the Home tab.
2. Select Get data.
3. Choose Text/CSV. If it is not visible, open the full Get Data list and locate the connector.
4. Browse to the required CSV or text file.
5. Select the file and choose Open.

Step 3: Review the File Preview

Power BI displays a preview of the selected file. Review the data before loading it into the model.

• File origin or encoding: Determines how text characters are interpreted.
• Delimiter: Defines how fields are separated.
• Data type detection: Controls whether Power Query attempts to infer column types.
• Preview: Helps confirm that the data appears in the expected rows and columns.

Step 4: Choose Load or Transform Data

Best practice: Choose Transform Data when importing unfamiliar files. Check the column names, data types, and sample values before loading the final table.

4. Configuring Headers, Delimiters, and Encoding

4.1 Promoting the First Row to Headers

Some files contain column names in the first row, while others contain data immediately. If Power Query does not identify the header row correctly, use Use First Row as Headers in the Power Query Editor.

For example, a file may initially appear like this:

Column1,Column2,Column3
OrderID,Region,Sales
1001,West,120000
1002,North,45000

If the first row is a generic label and the second row contains the actual column names, remove the unnecessary top row first and then promote the correct row to headers.

4.2 Selecting the Correct Delimiter

If the delimiter is incorrect, Power BI may place an entire record into one column or split values into unexpected columns.

1. Open the file using the Text/CSV connector.
2. Inspect the preview and identify the character separating fields.
3. Select the correct delimiter, such as comma, tab, semicolon, or pipe.
4. Verify that each field appears in its own column.
5. Open Power Query Editor if further transformations are required.

4.3 Understanding File Encoding

Encoding determines how text characters are stored and interpreted. Incorrect encoding can produce unreadable symbols, especially in names containing accented characters or in multilingual data.

• Use the encoding expected by the source application whenever known.
• UTF-8 is a common choice for modern text exports.
• Check names, descriptions, and other text columns for corrupted characters.
• Do not assume all files use the same encoding if they come from different systems.

4.4 Handling Quoted Values

CSV fields may contain delimiters inside quotation marks. For example:

CustomerID,CustomerName,City
1,"Sharma, Anil",Mumbai
2,"Patel, Riya",Pune

The comma inside a quoted customer name should not create an additional column. Use the appropriate CSV parsing settings and inspect the preview to ensure that quoted values are interpreted correctly.

5. Transforming Imported Text Data in Power Query

Power Query Editor provides a sequence of transformation steps that are applied again when the query refreshes. These steps make the import process repeatable and reduce manual cleanup.

5.1 Rename Columns

Use meaningful column names, such as Order Date, Customer Name, and Total Sales, instead of generic names such as Column1 or ambiguous abbreviations.

5.2 Set Correct Data Types

Choose data types deliberately. Automatic detection can be helpful, but it may infer an unsuitable type when a column contains mixed values or only a small sample is available.

5.3 Remove Blank Rows and Unnecessary Columns

1. Inspect the dataset for empty rows or records that contain no useful values.
2. Remove columns that are not needed for reporting, after confirming that they are not required for joins, relationships, or auditing.
3. Filter out invalid records using clear business rules.
4. Retain source identifiers when they are needed to trace a record back to the original file.

5.4 Trim and Clean Text

Text fields can contain extra spaces or non-printing characters that affect grouping and matching. Use Power Query's text transformation options, such as Trim and Clean, where appropriate.

For example, "West" and " West " may appear as separate categories until leading and trailing spaces are removed.

5.5 Replace Errors and Handle Missing Values

Before replacing errors or blanks, determine why they occur. A missing sales value is not necessarily the same as zero, and an invalid date may require correction rather than removal.

• Filter or investigate error rows to understand the cause.
• Replace missing values only when the business definition supports a replacement.
• Use a consistent rule for invalid dates and numeric values.
• Document transformations that affect financial or operational reporting.

6. Importing Data from a Folder

The Folder connector is useful when multiple files contain the same kind of data. Instead of importing each file separately, Power BI can list the files in a folder and help combine their contents into one table.

For example, a sales team might save a separate CSV export for each month:

SalesData/
  Sales_January.csv
  Sales_February.csv
  Sales_March.csv
  Sales_April.csv

If these files use a consistent layout, Power BI can combine the records into a single sales table for monthly, regional, and product-level analysis.

6.1 When Should You Use the Folder Connector?

• When a system exports one file per day, week, or month.
• When several branches submit files with the same columns.
• When transaction data is split into multiple CSV files.
• When new files are regularly added to a known folder.
• When you want a repeatable process instead of manually appending files.

6.2 Folder Import Workflow

1. Place the files to be combined in a dedicated folder.
2. Check that the files contain compatible columns and consistent structures.
3. Open Power BI Desktop.
4. Select Home > Get data > Folder.
5. Browse to the folder containing the source files and confirm the selection.
6. Review the file list and confirm that the correct files are included.
7. Choose Combine & Transform Data to inspect and transform the data before combining, or use the available combine option when the files are already consistent.
8. Review the sample file and generated transformation steps.
9. Apply any required cleanup in Power Query Editor.
10. Select Close & Apply to load the resulting data into the model.

6.3 Understanding the Folder File List

The Folder connector typically presents information about files found in the selected location. Available columns can include:

The exact metadata columns depend on the connector and environment. Use these fields to filter the file list before combining files when necessary.

7. Combining Multiple Files with the Same Structure

Power BI's combine-files experience uses a sample file to define how each file should be interpreted. It then applies the transformation logic to the files included in the query.

7.1 Example: Monthly Sales Files

Assume that the folder contains three files with the same columns:

Sales_January.csv
OrderID,OrderDate,Region,Sales
1001,2026-01-05,West,120000
1002,2026-01-07,North,45000

Sales_February.csv
OrderID,OrderDate,Region,Sales
1003,2026-02-02,South,67000
1004,2026-02-06,West,89000

Sales_March.csv
OrderID,OrderDate,Region,Sales
1005,2026-03-03,North,52000
1006,2026-03-08,South,71000

After combining, the resulting table should contain the transaction records from all three files, with a consistent set of columns. The header row from each file should not appear as a normal data record.

7.2 Why the File Structure Must Be Consistent

Combining files works best when all files use the same column names, compatible data types, similar header placement, and the same delimiter and encoding. Inconsistent layouts can lead to missing values, unexpected columns, duplicated headers, or transformation errors.

If file structures differ, standardize them before combining or create a transformation process that explicitly handles the differences.

7.3 Keep the Source File Name

When useful, retain the source file name as a column. This helps identify which monthly export or branch submission produced a record. Depending on the combine-files workflow, the source name may be available in the query or can be preserved from the file list before expanding the file contents.

8. Filtering Folder Contents Before Combining

A folder may contain unrelated documents, temporary files, or archived exports. Filter the file list so that only valid source files are processed.

8.1 Filter by Extension

If the folder contains both CSV and Excel files but the query expects CSV files, filter the Extension column to include only .csv.

8.2 Filter by File Name

Use the Name column to include files following a naming convention, such as Sales_ at the beginning of the file name. This helps exclude unrelated exports.

8.3 Exclude Temporary and Hidden Files

• Exclude temporary files that are not part of the official dataset.
• Check for hidden files or system-generated files that may be listed by the connector.
• Exclude archived versions if they would duplicate records already included in current files.
• Confirm the filtering rules when the folder's contents change.

9. Understanding the Power Query Combine Files Process

When combining files, Power Query commonly creates supporting queries and transformation logic. The names and exact arrangement can vary by connector and Power BI version.

These supporting items allow Power Query to repeat the same transformation steps across the files. Avoid deleting generated helper queries unless you understand how the main query depends on them.

10. Using Power Query M for Text and Folder Imports

Power Query uses the M language to define data retrieval and transformation steps. Many steps can be created through the interface without manually writing code, but understanding M makes it easier to review or customize a query.

10.1 Example: Read a CSV File

The following illustrative M query reads a CSV file from a local path. Change the path to match the location of your file.

let
    Source = Csv.Document(
        File.Contents("C:\PowerBI\Sales_January.csv"),
        [Delimiter=",", Encoding=65001, QuoteStyle=QuoteStyle.Csv]
    ),
    PromotedHeaders = Table.PromoteHeaders(
        Source,
        [PromoteAllScalars=true]
    ),
    ChangedTypes = Table.TransformColumnTypes(
        PromotedHeaders,
        {
            {"OrderID", Int64.Type},
            {"OrderDate", type date},
            {"Region", type text},
            {"Sales", type number}
        }
    )
in
    ChangedTypes

Important: This example assumes the file has a header row and that the sample values match the declared data types. If identifiers contain leading zeros, consider keeping the identifier column as text. Use the appropriate locale when parsing dates or decimal numbers that use regional formatting.

10.2 Example: Read Files from a Folder

The following example lists files in a folder and filters the list to CSV files. It demonstrates the initial folder connection; combining and transforming each file's content requires additional steps.

let
    Source = Folder.Files("C:\PowerBI\SalesData"),
    CsvFiles = Table.SelectRows(
        Source,
        each Text.Lower([Extension]) = ".csv"
    )
in
    CsvFiles

The result is a file list, not yet a consolidated sales table. Use the Combine Files workflow in Power Query or add the required parsing and expansion logic to transform each file's binary content and append the resulting tables.

10.3 Example: Add a Source File Name

Keeping the source file name can improve auditing. For example, a query can add a custom column based on the file metadata before the file content is expanded. The exact steps depend on how the folder-combine query was generated.

Table.AddColumn(
    CsvFiles,
    "SourceFile",
    each [Name],
    type text
)

This expression adds a column named SourceFile to a table that already contains a Name column. It does not itself combine the files.

11. Data Quality Checks After Importing

Importing data successfully does not guarantee that it is ready for analysis. Validate the combined result before creating visuals or publishing the report.

11.1 Validate Row Counts

• Compare the number of source files with the expected number of files.
• Check the row count for each file where practical.
• Compare the total combined row count with the expected total.
• Investigate unexpected increases or decreases after refresh.

11.2 Validate Column Names and Types

• Check that important columns are present.
• Confirm that column names have not changed in the latest export.
• Verify dates, amounts, quantities, and identifiers.
• Check for columns that contain unexpected nulls or errors.

11.3 Detect Duplicate Records

Duplicates may occur if a file is copied into the folder twice or if a source system exports overlapping periods. Do not automatically remove all identical-looking rows. First identify the business key and determine whether repeated transactions are valid.

11.4 Validate Totals

Compare important totals, such as sales revenue or transaction counts, with the source system. This helps reveal missing files, duplicate imports, parsing problems, and incorrect data types.

12. Refreshing Text and Folder Data

Power BI can rerun the saved query transformations when data is refreshed. For folder sources, the refresh process can pick up new or changed files that satisfy the query's filtering conditions.

12.1 Refresh in Power BI Desktop

1. Save the report and confirm that the source path is correct.
2. Update or add files to the source folder when required.
3. Select Refresh in Power BI Desktop.
4. Review the refresh status and any error messages.
5. Check row counts, key totals, and recent records to confirm that the new data was included.

12.2 Refresh Considerations in Power BI Service

After publishing a report, refresh requirements depend on where the source files are stored and how the dataset or semantic model is configured.

• Local or network folder: A suitable on-premises data gateway and correct source credentials may be required for scheduled refresh.
• Cloud-accessible source: Configure credentials and refresh settings according to the connector and source location.
• File path: Ensure that the published model can access the source location. A local path available only on one user's computer may not be accessible to the Power BI Service.
• Permissions: The account or gateway identity must have permission to read the source files.
• New files: Confirm that new files follow the expected naming and structural rules.

Publishing a report does not automatically make every local file path available to the Power BI Service. Test refresh in the target environment before relying on scheduled updates.

13. Common Problems and Troubleshooting

14. Best Practices for Importing Text Files and Folder Data

• Store related files in a dedicated folder with a clear naming convention.
• Keep file structures consistent across reporting periods.
• Use descriptive column names and appropriate data types.
• Filter out temporary, archived, or unrelated files before combining.
• Retain source file information when it supports auditing and troubleshooting.
• Use Power Query transformations instead of repeatedly editing files manually.
• Investigate errors and missing values before replacing or deleting them.
• Validate row counts and important totals after each significant refresh.
• Keep identifiers as text when leading zeros or nonnumeric characters are meaningful.
• Use stable, accessible source locations for reports that need regular refreshes.
• Test changes to the file structure before adding them to a production folder.
• Document any business rules used to filter or transform records.

15. Practical Project: Combine Monthly Sales Files

Project Objective

Create a Power BI report that combines monthly sales CSV files from a folder and enables users to analyze sales by month, region, and product.

Required Files

Create a folder named SalesData and place at least three files inside it:

• Sales_January.csv
• Sales_February.csv
• Sales_March.csv

Each file should contain columns such as OrderID, OrderDate, Region, Product, Quantity, and Sales.

Project Steps

1. Create the CSV files with consistent column names and compatible data types.
2. Open Power BI Desktop and connect to the folder using Get Data > Folder.
3. Filter the folder list to include only the required CSV files.
4. Choose Combine & Transform Data.
5. Inspect the sample file and ensure the headers are handled correctly.
6. Set the correct data types for dates, quantities, and sales values.
7. Remove unnecessary blank rows and correct text inconsistencies.
8. Retain a source file name if it is useful for tracing transactions.
9. Apply the transformations and load the combined table.
10. Create a line chart showing sales by month, a bar chart showing sales by region, and a table showing product-level results.
11. Refresh after adding a new monthly CSV file and confirm that the new records are included.

Expected Outcome

The report should show the combined transactions from all included files. Users should be able to filter sales by date, region, and product, while the query should apply the same cleaning steps when the data is refreshed.

16. Practice Exercises

1. Import a CSV file and identify its delimiter, encoding, and header row.
2. Import a tab-separated text file and verify that each field appears in the correct column.
3. Correct a column containing dates stored in different formats.
4. Combine three monthly sales files from a folder.
5. Filter out files that do not use the required file extension.
6. Add a source-file column so records can be traced to their original export.
7. Identify and investigate duplicate transactions in the combined result.
8. Add a new file and verify that the refresh includes its records.
9. Explain why a folder-based report may refresh in Power BI Desktop but fail in Power BI Service.

17. Interview Questions and Answers

Q1. How do you import a CSV file into Power BI?

Answer: Select Home > Get Data > Text/CSV, choose the file, review its delimiter and preview, and select Load or Transform Data depending on whether cleaning is required.

Q2. What is the purpose of the Folder connector?

Answer: It connects to a folder containing multiple files and helps combine compatible files into one table. It is especially useful for recurring exports such as daily or monthly reports.

Q3. What is the difference between Load and Transform Data?

Answer: Load imports the data into the model, while Transform Data opens Power Query Editor so you can clean and reshape the data before loading it.

Q4. Why must files have a consistent structure before combining them?

Answer: Consistent column names, header placement, delimiters, and data types help Power Query apply the same transformation logic to every file. Inconsistent files may produce errors or incomplete results.

Q5. What is a delimiter?

Answer: A delimiter is a character, such as a comma, tab, semicolon, or pipe, used to separate values in a text file.

Q6. Why can a CSV file display all values in one column?

Answer: The selected delimiter may not match the character used in the source file. Selecting the correct delimiter usually resolves the issue.

Q7. What is the purpose of Power Query?

Answer: Power Query is used to connect to data sources and apply repeatable transformations, such as filtering rows, changing data types, renaming columns, and combining files.

Q8. Can Power BI automatically include new files in a folder?

Answer: A properly configured folder query can include new files that satisfy its path and filtering conditions when the query refreshes. The new files must be accessible and compatible with the transformation logic.

Q9. Why should the source file name sometimes be retained?

Answer: It helps users trace records to their original file and investigate missing, duplicated, or incorrect data.

Q10. What is the difference between importing one text file and importing a folder?

Answer: A single-file connection reads one selected file, while a folder connection can process multiple files and combine their contents when they follow compatible structures.

Q11. What should you check when a folder refresh fails?

Answer: Check the folder path, file permissions, source credentials, file filters, data structure, and gateway configuration if the report is published and requires access to a local or network source.

Q12. Why is data-type validation important?

Answer: Correct data types ensure dates can be analyzed as dates, numeric values can be aggregated correctly, and identifiers are not accidentally altered by automatic conversion.

18. Quick Revision Checklist

• Understand CSV, TXT, TSV, and other delimited text formats.
• Choose the correct Text/CSV connector for a single file.
• Verify the delimiter, encoding, and header row.
• Use Transform Data to clean data in Power Query Editor.
• Assign appropriate data types to columns.
• Use the Folder connector for recurring collections of files.
• Filter unrelated files before combining them.
• Ensure files have compatible structures.
• Validate row counts, duplicates, and totals.
• Test refresh and configure source access correctly after publishing.

19. Conclusion

Importing text files and data from folders is an essential Power BI skill. The Text/CSV connector makes it possible to load individual exports, while the Folder connector supports repeatable workflows for combining multiple compatible files. Power Query Editor provides the tools needed to clean, standardize, and validate imported data before it enters the Power BI model.

For reliable reports, always verify the source structure, configure data types carefully, filter the correct files, investigate errors, and test refresh behavior. These practices help create accurate, maintainable reports that can grow as new files become available.

Explore the course: Microsoft Power BI Training | Register for a Demo | JustAcademy